//查询整个表
const rows = (tableName) => 'SELECT * FROM ' + tableName
exports.rows = rows
// 添加
const save = (tableName, options) => {
    const keys = Object.keys(options).join(',')
    const values = Object.values(options)
    let sql = `INSERT INTO ${tableName} (${keys}) VALUES (`
    for (const item of values) {
        sql += `'${item}',`
    }
    // 删除最后的逗号
    let newStr = sql.substring(0, sql.length - 1);
    return newStr + ')'
}
exports.save = save
// 批量添加
exports.sqlSaveList = (tableName, list) => {
    const keys = Object.keys(list[0])
    const values = []
    for (const item of list) {
        let keyArr = []
        for (const key in item) {
            keyArr.push(`'${item[key]}'`)
        }
        values.push(`(${keyArr.join(',')})`)
    }

    let sql = `INSERT INTO ${tableName} (${keys.join(',')}) VALUES ${values.join(', ')}`
    return sql
}
// 分页条件查询
exports.sqlList = (tableName, options) => {
    const { data, LIMIT, OFFSET, SELECT, ORDER } = options
    const list = []
    for (const key in data) {
        if (data[key]) {
            list.push(`${key} = '${data[key]}'`)
        }
    }
    let str;
    if (list.length) {
        // if (ORDER) {
        //     str = `WHERE ${list.join(' OR ')} ORDER BY ${ORDER} ASC`
        // }else {
        //
        // }
        str = `WHERE ${list.join(' OR ')}`
    }else {
        str = ''
    }
    let or;
    if (ORDER) {
        or = `ORDER BY ${ORDER} ASC`
    }else {
        or = ''
    }
    console.log(ORDER)
    const sql = `SELECT ${SELECT || '*'} FROM ${tableName} ${str} ${or} LIMIT ${LIMIT} OFFSET ${OFFSET}`
    console.log(sql)
    return sql
}
// 不分页条件查询
exports.sqlRows = (tableName, options) => {
    const { data, SELECT } = options
    const list = []
    if (data) {
        for (const key in data) {
            list.push(`${key} = '${data[key]}'`)
        }
    }
    let str ;
    if (list.length) {
        str = `WHERE ${list.join(' OR ')}`
    }else {
        str = ''
    }
    const sql = `SELECT ${SELECT || '*'} FROM ${tableName} ${str}`
    return sql
}
// 查询总数
exports.sqlTotal = tableName => `SELECT COUNT(*) FROM ${tableName}`
// 数组查询 不分页
exports.sqlArrList = (tableName, options) => {
    const { SELECT, list, key, tableKey } = options
    const arr = []
    for (let i = 0; i < list.length; i++) {
        if (i === list.length - 1) {
            arr.push("SELECT '"+ list[i][key] +"' AS "+ key +" ")
        }else {
            arr.push("SELECT '"+ list[i][key] +"' AS "+ key +" UNION")
        }
    }
    const sql = `SELECT ${SELECT || '*'} FROM ${tableName} WHERE ${tableKey} IN (${arr.join(' ')})`
    return sql
}
//删除
exports.sqlDelete = (tableName, options) => {
    const {data} = options
    const arr = []
    for (const key in data) {
        arr.push(`${key} = '${data[key]}'`)
    }
    const sql = `DELETE FROM ${tableName} WHERE ${arr.join('OR')}`
    return sql
}
//更新
exports.sqlUpdate = (tableName, options) => {
    const {data, key} = options
    const arr = []
    for (const keys in data) {
        arr.push(`${keys} = '${data[keys]}'`)
    }
    const list = []
    for (const keys in key) {
        list.push(`${keys} = '${key[keys]}'`)
    }
    const sql = `UPDATE ${tableName} SET ${arr.join(',')} WHERE ${list.join('OR')}`
    return sql
}
// 删除表中的所有数据
exports.sqlTableDel = (tableName) => `DELETE FROM ${tableName}`
// 根据字段删除表数据
exports.sqlIdDel = (tableName, data) => {
    const arr = []
    if (Array.isArray(data)){
        for (const item of data) {
            console.log(item)
            for (const key in item) {
                console.log(key)
                arr.push(`${key} = '${item[key]}'`)
            }
        }
        console.log(arr)
    }else {
        for (const key in data) {
            arr.push(`${key} = '${data[key]}'`)
        }
    }
    let str;
    if (arr.length){
        str = `WHERE ${arr.join(' OR ')}`
    }else {
        str = ''
    }
    return `DELETE FROM ${tableName} ${str}`
}